package com.ph.irp.base.usermanage.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.usermanage.dao.PBasePurviewRoleDAO;
import com.ph.irp.model.PBasePurviewRole;
import com.ph.util.DBUtil;

public class PBasePurviewRoleDAOImpl implements PBasePurviewRoleDAO {

	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public List<PBasePurviewRole> getAllUserGroupList() throws Throwable {
		List<PBasePurviewRole> resList = new ArrayList<PBasePurviewRole>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			// select r.roleid id,r.rolename rolename,r.roledesc roledesc
			// from p_base_purview_role r
			sb.append("select r.roleid id,r.rolename rolename,");
			sb.append("r.roledesc roledesc ");
			sb.append("from p_base_purview_role r");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBasePurviewRole role = new PBasePurviewRole();
				role.setRoleDesc(rs.getString("roledesc"));
				role.setRoleId(rs.getInt("id"));
				role.setRoleName(rs.getString("rolename"));

				resList.add(role);

			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;

	}

	@Override
	public List<PBasePurviewRole> getUserGroupListByRoleName(String roleName)
			throws Throwable {
		List<PBasePurviewRole> resList = new ArrayList<PBasePurviewRole>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			// select r.roleid id,r.rolename rolename,r.roledesc roledesc
			// from p_base_purview_role r
			sb.append("select r.roleid id,r.rolename rolename,");
			sb.append("r.roledesc roledesc ");
			sb.append("from p_base_purview_role r ");
			sb.append("where rolename like '%").append(roleName).append("%'");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBasePurviewRole role = new PBasePurviewRole();
				role.setRoleDesc(rs.getString("roledesc"));
				role.setRoleId(rs.getInt("id"));
				role.setRoleName(rs.getString("rolename"));

				resList.add(role);

			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;

	}

	public PBasePurviewRole getRoleByRoleId(String roleId) throws Throwable {
		PBasePurviewRole result = new PBasePurviewRole();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			// select r.roleid id,r.rolename rolename,r.roledesc roledesc
			// from p_base_purview_role r
			sb.append("select r.roleid id,r.rolename rolename,");
			sb.append("r.roledesc roledesc ");
			sb.append("from p_base_purview_role r ");
			sb.append("where RoleID=").append(roleId);
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				result.setRoleDesc(rs.getString("roledesc"));
				result.setRoleId(rs.getInt("id"));
				result.setRoleName(rs.getString("rolename"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return result;
	}

	public void addUserRole(PBasePurviewRole role) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("insert into p_base_purview_role(rolename,roledesc) ");
			sb.append(" values('");
			sb.append(role.getRoleName());
			sb.append("','").append(role.getRoleDesc());
			sb.append("')");

			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

	public void editUserRole(PBasePurviewRole role) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("update p_base_purview_role set RoleName='");
			sb.append(role.getRoleName()).append("',RoleDesc='");
			sb.append(role.getRoleDesc()).append("' ");
			sb.append("where RoleID=").append(role.getRoleId());

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

	public void deleteUserGroup(String deleteIds) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;

		try {
			StringBuilder sb = new StringBuilder();
			sb.append(" delete from p_base_purview_role ");
			sb.append(" where RoleID in (").append(deleteIds).append(")");

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, null);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public int getRoleIdByRoleNameAndDesc(String roleName, String roleDesc)
			throws Throwable {
		int res = 0;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("select r.roleid id ");
			sb.append("from p_base_purview_role r ");
			sb.append("where rolename like '%").append(roleName).append("%' ");
			sb.append("and roledesc like '%").append(roleDesc).append("%'");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				res = rs.getInt("id");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return res;
	}

}
